<?php  
/*Copyright 2013 CFour Dev.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.*/

class MySQL 
{
	protected $prefix;
    protected $host;  
	protected $user;
	protected $password;
	protected $db;  
	protected $table_users = "users";
	protected $table_groups = "groups";
	protected $table_news = "news";
	protected $num = null;
	protected $counter = 0;
	protected $arr = 0;
	protected $temp = null;
	private $result = null;
	public $link = null;
	
	public function __construct($host_, $user_, $password_, $db_, $prefix_ = "") 
    {
		$this->prefix = $prefix_;
		$this->host = $host_;  
		$this->user = $user_;
		$this->password = $password_;
		$this->db = $db_;
		$this->link = mysql_connect($this->host, $this->user, $this->password);
		mysql_select_db($this->db, $this->link);
		mysql_set_charset("utf8",$this->link);
	}
	
	public function __destruct()
	{
		mysql_close($this->link);
	}

    public function AuthUser($name, $password)  
    {
		$name = mysql_real_escape_string($name);
		$password = md5(mysql_real_escape_string($password));
		$this->result = mysql_query("select {$this->prefix}{$this->table_users}.*,{$this->prefix}{$this->table_groups}.group_name from {$this->prefix}{$this->table_users},{$this->prefix}{$this->table_groups} where lcase(user_name) = lcase('{$name}') and user_password='{$password}' and {$this->prefix}{$this->table_users}.user_group={$this->prefix}{$this->table_groups}.group_id") or die('MySQL Error'.mysql_error());
        if(mysql_num_rows($this->result)) return mysql_fetch_assoc($this->result);
		else return 0;
    }
	public function GetUserData($id, $password)  
    {
		$id = mysql_real_escape_string($id);
		$password = mysql_real_escape_string($password);
		$this->result = mysql_query("select {$this->prefix}{$this->table_users}.*,{$this->prefix}{$this->table_groups}.group_name from {$this->prefix}{$this->table_users},{$this->prefix}{$this->table_groups} where user_id = '{$id}' and user_password='{$password}' and {$this->prefix}{$this->table_users}.user_group={$this->prefix}{$this->table_groups}.group_id") or die('MySQL Error: '.mysql_error());
        if(mysql_num_rows($this->result)) return mysql_fetch_assoc($this->result);
		else return 0;
    }
	public function GetNews($from = 0, $to = 15)
	{
		$from = mysql_real_escape_string($from);
		$to = mysql_real_escape_string($to);
		$this->result = mysql_query("select {$this->prefix}{$this->table_news}.*,{$this->prefix}{$this->table_users}.user_name from {$this->prefix}{$this->table_news},{$this->prefix}{$this->table_users} where {$this->prefix}{$this->table_news}.news_author={$this->prefix}{$this->table_users}.user_id and news_moderated=1 order by  `news_date` desc limit {$from},{$to};") or die('MySQL Error: '.mysql_error());
		$this->num = mysql_num_rows($this->result);
		$this->arr = null;
		while($this->temp = mysql_fetch_assoc($this->result)) $this->arr[count($this->arr)] = $this->temp;
		return $this->arr;
	}
	public function GetOnModeration()
	{
		$this->result = mysql_query("SELECT COUNT(*) FROM {$this->prefix}{$this->table_news} WHERE `news_moderated`=0;");
		$this->arr = null;
		$this->arr = mysql_fetch_array($this->result);
		return $this->arr[0];
	}
	public function GetNewsCount()
	{
		$this->result = mysql_query("SELECT COUNT(*) FROM {$this->prefix}{$this->table_news} WHERE `news_moderated`=1;");
		$this->arr = null;
		$this->arr = mysql_fetch_array($this->result);
		return $this->arr[0];
	}
	public function CreateDB()
	{
		mysql_query("CREATE TABLE IF NOT EXISTS `{$this->prefix}{$this->table_users}` (
		  `user_id` int(16) NOT NULL AUTO_INCREMENT,
		  `user_group` int(4) NOT NULL,
		  `user_name` varchar(32) COLLATE utf8_bin NOT NULL,
		  `user_password` varchar(32) COLLATE utf8_bin NOT NULL,
		  `user_email` varchar(64) COLLATE utf8_bin NOT NULL,
		  PRIMARY KEY (`user_id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='users table' AUTO_INCREMENT=1 ;") or die('Mysql error'.mysql_error());
		mysql_query("CREATE TABLE IF NOT EXISTS `{$this->prefix}{$this->table_groups}` (
		  `group_id` int(4) NOT NULL AUTO_INCREMENT,
		  `group_name` varchar(32) COLLATE utf8_bin NOT NULL,
		  `group_privilegies` varchar(32) COLLATE utf8_bin NOT NULL,
		  PRIMARY KEY (`group_id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='groups table' AUTO_INCREMENT=1 ;") or die('Mysql error'.mysql_error());
		mysql_query("CREATE TABLE IF NOT EXISTS `{$this->prefix}{$this->table_news}` (
		  `news_id` int(9) NOT NULL AUTO_INCREMENT,
		  `news_title` varchar(128) COLLATE utf8_bin NOT NULL,
		  `news_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		  `news_author` int(8) NOT NULL,
		  `news_short` text COLLATE utf8_bin,
		  `news_full` longtext COLLATE utf8_bin,
		  `news_poster` tinytext COLLATE utf8_bin,
		  `news_moderated` tinyint(1) NOT NULL,
		  PRIMARY KEY (`news_ID`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='news table' AUTO_INCREMENT=1 ;") or die('Mysql error'.mysql_error());
		return true;
	}
}  
?>  